package io.g740.util;

import io.g740.dto.Column;
import io.g740.dto.CreateTableInfoDto;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

/**
 * sql工具
 *
 * @author YunKaiLuo
 * @date 2024/10/19
 */
public class SqlUtil {
    private static final Logger log = LoggerFactory.getLogger(SqlUtil.class);

    private SqlUtil() {
    }

    /**
     * 获取指定表的创建语句
     *
     * @param connx
     * @param tableName
     * @return
     */
    public static CreateTableInfoDto getCreateStatement(Connection connx, String tableName) {
        // 创建Statement对象
        try (Statement statement = connx.createStatement()) {
            // 执行SQL查询语句
            String sql = "SHOW CREATE TABLE `" + connx.getCatalog() + "`.`" + tableName + "`";
            try (ResultSet resultSet = statement.executeQuery(sql)) {
                // 处理查询结果
                while (resultSet.next()) {
                    String id = resultSet.getString("Table");
                    String ddl = resultSet.getString("Create Table");
                    CreateTableInfoDto createTableInfoDto = new CreateTableInfoDto(id, ddl);
                    return createTableInfoDto;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("读取表创建语句错误:", e);
        }
        return null;
    }

    public static String formatComment(String comment) {
        return isBlank(comment) ? "" : comment.replaceAll("\r\n", "\t");
    }

    public static boolean isBlank(String str) {
        return str == null || str.trim().isEmpty();
    }

    /**
     * 判断是否存在某张表
     *
     * @param connx
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static boolean existsTable(Connection connx, String tableName) throws SQLException {
        boolean existence = false;
        DatabaseMetaData metaData = connx.getMetaData();
        try (ResultSet resultSet = metaData.getTables(connx.getCatalog(), connx.getSchema(), tableName, new String[]{"TABLE"})) {
            while (resultSet.next()) {
                existence = true;
                log.debug("库名：{}", resultSet.getString("TABLE_CAT"));
                log.debug("表名：{}", resultSet.getString("TABLE_NAME"));
                log.debug("注释：{}", resultSet.getString("REMARKS"));
                log.debug("表类型：{}", resultSet.getString("TABLE_TYPE"));
            }
        } catch (SQLException e) {
            throw new RuntimeException("寻找数据库表信息出现错误", e);
        }
        return existence;
    }

    /**
     * 获取数据库中的字段信息
     *
     * @param connx
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static Map<String, Column> getColumnsInfo(Connection connx, String tableName) throws SQLException {
        DatabaseMetaData databaseMetaData = connx.getMetaData();
        String catalog = connx.getCatalog(), schema = connx.getSchema();
        Set<String> primaryKeys = new HashSet<>();
        try (ResultSet primaryKeysResultSet = databaseMetaData.getPrimaryKeys(catalog, schema, tableName)) {
            while (primaryKeysResultSet.next()) {
                String columnName = primaryKeysResultSet.getString("COLUMN_NAME");
                primaryKeys.add(columnName);
            }
            if (primaryKeys.size() > 1) {
                log.warn("当前表:{}，存在多主键情况！", tableName);
            }
        } catch (SQLException e) {
            throw new RuntimeException("读取表主键信息:" + tableName + "错误:", e);
        }
        Map<String, Column> columnsInfoMap = new LinkedHashMap<>();
        try (ResultSet resultSet = databaseMetaData.getColumns(catalog, schema, tableName, "%")) {
            while (resultSet.next()) {
                Column column = new Column();
                String name = resultSet.getString("COLUMN_NAME");
                column.setName(name);
                column.setPrimaryKey(primaryKeys.contains(name));
                column.setTypeName(resultSet.getString("TYPE_NAME"));
                int dataType = resultSet.getInt("DATA_TYPE");
                column.setDataType(dataType);
                column.setLength(resultSet.getInt("COLUMN_SIZE"));
                column.setScale(resultSet.getInt("DECIMAL_DIGITS"));
                column.setRemarks(formatComment(resultSet.getString("REMARKS")));
                column.setDefaultValue(resultSet.getString("COLUMN_DEF"));
                column.setNullable(resultSet.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                try {
                    column.setAutoIncrement("YES".equals(resultSet.getString("IS_AUTOINCREMENT")));
                } catch (SQLException sqlException) {
                    // 目前测试在oracle旧驱动下存在问题，降级成false.
                }
                column.setOrdinalPosition(resultSet.getInt("ORDINAL_POSITION"));
                columnsInfoMap.put(name, column);
            }
            return Collections.unmodifiableMap(columnsInfoMap);
        } catch (SQLException e) {
            throw new RuntimeException("读取表字段信息:" + tableName + "错误:", e);
        }
    }


    public static boolean execute(Connection connx, String sql) throws SQLException {
        // 创建Statement对象
        try (Statement statement = connx.createStatement()) {
            return statement.execute(sql);
        } catch (SQLException e) {
            throw new RuntimeException("读取表创建语句错误:", e);
        }
    }

    public static Object getAny(ResultSet resultSet, String columnName, String columnType) throws SQLException {
        int column = resultSet.findColumn(columnName);
        switch (columnType) {
            case "SMALLINT":
            case "ATINYINT":
            case "TINYINT":
                return resultSet.getShort(column);
        }
        return null;
    }
}
